Best Practice Guide - Data Engineering in Campaign Manager and Engine

From a Campaign Manager perspective, Data Engineering defines the creation of any column data that has been derived from other data in the system. Common examples are decodes, expressions and aggregates.

Campaign Manager delivers powerful functionality that allows Contact and Response data to be added to their tables intra-day and so the data in these Campaign History Data Schema tables will be changing many times a day.

This functionality is likely to be exposed to other tables in the future so should be understood by all users even if they are not currently using built-in Campaign History Data Schema. Adopting this best practice now is a solid approach going forward.

Adding data to a table while the table is in use will have an effect on any Data Engineering created on the table, or that is dependent on the table. As soon as any new data is added, the engineered data’s counts will be stale, i.e. the counts in the engineering will no longer accurately reflect the counts in the tables.

NOTE:

The Best Practice in this guide should be used with all data Engineering and not just that engineering created on the Campaign History Data Schema.

When Data Engineering is being created, the creator needs to ask themselves some questions, the answers to which will govern how the Data Engineering should be created and what impact it will have on performance of the system.

Questions:

1) Do I need these counts to change during the day?

Discussion – If, for example, there is an aggregate column created for reporting on Campaign History and the reporting will run as part of the load process to produce exported reports, is there really a need for this column to reflect new counts during the business day or is a snapshot count view acceptable. It is presumed that the column will be dropped and re-created as part of the load processes again tomorrow, so recalculating it multiple times during the day serves no purpose and may cause unnecessary processing.

2) Who will use this column?

Discussion – If this Data Engineering is just for a marketer’s campaign audience or segment document, creating it as a named table column visible to everyone is just creating data on the table potentially confusing other users. If this data Engineering column will be used by everyone many times a day, it needs to be there at the start of each day. If this is the case, then creating it as part of the overnight load process seems most logical.

With these questions in mind, the next section looks at how to create these columns.

There are five ways to create Data Engineering within Campaign Manager. Some methods belong to the Database Administrator or the persona in charge of the data load, and some to the Marketer/Analyst.

NOTE:

For engineering that is required on the Contact History and Response History tables, the best practice is that this engineering is added via iLoader and not via the application tools, this allows Engine to re-build them as required.

The methods are described below, and it is very important to understand when each method would be used from a best practice perspective. It is important that those decisions are made with the understanding of all stakeholders including administrators, marketers and analysts.

1. iLoader – The data load process, which normally runs nightly, can be used to create Data Engineering via iLoader scripting. These columns will be ready for the users when a new database is published. Best practice here is to create columns that are always required for immediate analytics by all users, or that are required for any subsequent reporting processes that may also be run as part of the overnight load process.
2. Campaign Manager Scheduled Documents – Executed either via the Document Scheduler or from iLoader, this is an alternative to point 1, allowing marketers to define columns they want to appear every day and be available to all marketers/analysts. Data Engineering nodes in Campaign Manager can be used to create new named column objects. These definitions are added to a “Master Engineering” document that is executed by the load process. This allows for user created engineering to be added to the database for the start of each day without any change required to the iLoader scripts. Again, this tends to be used for the same business case as point 1, where columns are needed for reporting or for the start of the day for all users.
3. Campaign Manager Engineering nodes to create new Columns – Marketers and Analysts can use the Data Engineering nodes to create new named column objects on the fly, using the optional “Table Column Name” box. Best practice here is to only use this method to create/test new Engineering to be added to the “Master Engineering” document as outlined in Point 2. This is not a suggested method for analysts who are creating column Data Engineering only for themselves. In that case see Point 4.
4. On the fly, Marketer/Analyst based Engineering – When an Analyst or Marketer wants to carry out some engineering for their own segmentation for a specific campaign audience or piece on analytics, they should use the Campaign ManagerData Engineering nodes in an in-line way. That is when the Data Engineering node, such as the expression tool, is added to the Campaign Audience or Segment document. The advantage here is that the definition of the engineering is now within the document for ease of recalculation with no visible table column created. This ensures that the definition of the Data Engineering is always held in the place it is required, i.e. the segment document of Campaign Audience, and even more key is that it will always therefore be able to recalculate itself as required. Example of in-line engineering:

5. Data Helpers – Marketers and Analysts may not be aware of this but when they use the Data Helpers, for example when clicking on a date column and using the Year function, Data Engineering is created that is invisible to users.

To give the marketer and analysts the flexibility to manage these data changes and the results optimally, there are some new commands and concepts.

New Term

Snapshot Column – A piece of data engineering that provides a snapshot of counts. The counts will not change even if their dependencies change.

Two new iLoader Commands:

• ENGINEER_AS_SNAPSHOT TRUE/FALSE

E.g. ENGINEER_AS_SNAPSHOT TRUE

Once set to TRUE all subsequent data engineering will create snapshot columns.

(If this command is not present engineered columns will be created with SNAPSHOT = False as default)

• DROP_SNAPSHOTS Full Name

e.g. DROP_SNAPSHOTS [Demo].[Customer]

This command will drop all snapshot columns on the defined object.

Three new Engine column properties (visible via AMC):

• DeleteOnDataChange – Y/N – Flag to alert engine to drop this column when data changes (Not manually editable by end users).
• iLoaderSnapshotFlag – Y/N – Flag to alert Engine that this is a snapshot and should be null padded on data change.
• CalcError – Indicates if a column is in an error state and is therefore unusable or needs recalculation.

Application Default Use:

• Campaign Named Engineering Columns – This method will create columns with the snapshot Flag set to true.
• Campaign In-line Engineering – This method will create columns with the snapshot flag set to false.
• Data Helpers – Columns will be dropped when data changes and will be recreated when the user uses the helpers after a data change.

As discussed, data changes will occur to the Campaign History Data Schema. The way that Data Engineering is dealt with in this scenario will be governed by how it was created.

With the improved Engine dependency and locking model, each piece of Data Engineering will know if one of its dependencies has changed, where a dependency can be either another column or a link. Depending on how it was created, it will react in different ways.

If we consider this in more detail, it is clear there are limited options, which are:

1. Column will persist and Engine will recalculate on demand:

When data changes, the Data Engineering column will know about it. At this point, it will go in to an uncalculated state. The table object will still be there, but its counts are stale and no longer accessible. In this case, as soon as any part of the application calls for the column, maybe running as part of a campaign audience or segment document, Engine can recalculate the column, presuming it knows how the column was created.

2. Column is dropped by Engine and recreated on demand:

When data changes, the Data Engineering column knows about it. At this point, the column is removed completely and relies on the application to create a new one.

3. Column will persist and Engine will pad with null.
4. When data changes, the columns are not interested in the changes. They are calculated at the time and the creator is happy for the counts to persist for the lifetime of the object. In this case, a new null row count would simply be added to the data Engineering column.

The following tables show how columns created Campaign Manageror via iLoader, will react to a data change. Column headers are as follows:

• Engineering type: Engine has different storage type for engineered columns. Remap/Decode refers to columns such as a Numeric Bands or a data Decode. All others should be self-explanatory.
• Call explicit: Makes permanent refers to the use of the Make Permanent command in iLoader on an engineered column.
• Created Type: Relates to the Engineering type, viewable in AMC.